﻿CREATE PROCEDURE [SSIS].[ConfigureSolutionEnvironmentVariables](
	@SolutionFolderName sysname,
	@EnvironmentName sysname,
	@SolutionEnvVarXml xml
)
AS
BEGIN
	--parse xml and configure all defined variables
	DECLARE @docHandle int
	EXEC sp_xml_preparedocument @docHandle OUTPUT, @SolutionEnvVarXml;

	DECLARE C_VARIABLES CURSOR FAST_FORWARD FOR(
		SELECT
			[VarName],
			[VarValue]
		FROM OPENXML(@docHandle,'VariableList/Variable')
		WITH(
			[VarName] sysname '@name',
			[VarValue] sql_variant '@value'
		)
	);
	DECLARE @VarName sysname, @VarValue sql_variant;
	OPEN C_VARIABLES;
	FETCH NEXT FROM C_VARIABLES INTO @VarName, @VarValue;
	WHILE(@@FETCH_STATUS = 0)
	BEGIN
		EXEC [$(SSISDB)].[catalog].[set_environment_variable_value]
			@variable_name = @VarName,
			@environment_name = @EnvironmentName,
			@folder_name = @SolutionFolderName,
			@value = @VarValue;
		
		FETCH NEXT FROM C_VARIABLES INTO @VarName, @VarValue;
	END
	CLOSE C_VARIABLES;
	DEALLOCATE C_VARIABLES;

	--clean up xml object
	EXEC sp_xml_removedocument @docHandle;
				
	RETURN(0);
END
GO
EXECUTE sp_addextendedproperty @name = N'Description', @value = N'This procedure will configure all solution environment variable values contained within @SolutionEnvVarXml.', @level0type = N'SCHEMA', @level0name = N'SSIS', @level1type = N'PROCEDURE', @level1name = N'ConfigureSolutionEnvironmentVariables';

